package com.arxan;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.File;

import org.apache.log4j.Logger;


/**
* 在sqlite3文件数据库中记录每一个文件已经处理的行数位置
* @author Hongliang
* @Time 2017-03-01 12:10:00
*
*/
public class DBSaveStep {
	private Connection conn = null;
	private Logger log;

	/**
	   * 建立数据库的连接
	   * @param dbfilename
	   *       数据库文件的文件名
	   */
	public DBSaveStep(String dbfilename) {
		boolean needCreateTable = false;
		File dbfile = new File(dbfilename);
		if (!dbfile.exists()) {
			needCreateTable = true;
		}
		log = Logger.getLogger("railyFile");
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + dbfilename);
			conn.setAutoCommit(false);
		} catch (Exception e) {
			String badstr = e.getClass().getName() + ":" + e.getMessage();
			log.error(badstr);
			System.err.println(badstr);
			System.exit(0);
		}
		if (needCreateTable) {
			try {
				Statement stmt = conn.createStatement();
				String sql = "CREATE TABLE SAVESTEP" + "(FILENAME 	CHAR(256) PRIMARY KEY ," + "STEP  INT )";
				stmt.executeUpdate(sql);
				stmt.close();
				conn.commit();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	/**
	 * 得到该文件之前已经处理过的行数
	 * 
	 * @param filepath
	 *            文件路径
	 * @return 已经处理过的行数 -1 表示没有处理过 >=0 表示已经处理的字节数
	 * @exception (方法有异常的话加)
	 * @author hongliang
	 * @Time 2017-03-01 12:24:29
	 */
	public int getStep(String filepath) {
		int oldstep = -1;
		try {

			String sql = "SELECT * FROM SAVESTEP WHERE FILENAME=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, filepath);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				oldstep = rs.getInt("STEP");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return oldstep;
	}

	/**
	 * 更新文件已经处理过的字节数
	 * 
	 * @param filepath
	 *            文件路径
	 * @param step
	 *            已经处理过的字节数
	 * @return 
	 * @exception 异常会导致进程退出
	 * @author hongliang
	 * @Time 2017-03-01 12:24:29
	 */
	public void updateFileStep(String filepath, int step) {
		PreparedStatement stmt = null;
		String updatesql = null;
		try {
			int oldstep = getStep(filepath);
			if (oldstep != -1) {
				updatesql = "UPDATE SAVESTEP SET STEP=? WHERE FILENAME=?";

			} else {
				updatesql = "INSERT INTO SAVESTEP (STEP,FILENAME) " + "VALUES (?, ?);";
			}
			stmt = conn.prepareStatement(updatesql);
			stmt.setInt(1, step);
			stmt.setString(2, filepath);
			stmt.executeUpdate();

			stmt.close();
			conn.commit();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		log.debug("Records created successfully");
	}

	public void finalize() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
